package com.generator.parser;

import com.alibaba.druid.sql.SQLUtils;
import com.alibaba.druid.sql.ast.SQLStatement;
import com.alibaba.druid.sql.ast.statement.SQLColumnDefinition;
import com.alibaba.druid.sql.ast.statement.SQLCreateTableStatement;
import com.alibaba.druid.sql.ast.statement.SQLPrimaryKey;
import com.alibaba.druid.sql.ast.statement.SQLTableElement;
import com.alibaba.druid.util.JdbcConstants;
import cn.hutool.core.util.StrUtil;
import com.generator.model.ColumnInfo;
import com.generator.model.TableInfo;
import org.springframework.stereotype.Component;

import java.util.ArrayList;
import java.util.List;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

/**
 * SQL DDL解析器
 * 解析CREATE TABLE语句，提取表结构信息
 * 
 * @author Generator
 * @since 2024-01-01
 */
@Component
public class SqlDdlParser {

    // 仅保留回退用的表注释提取（当 AST 无注释时）
    private static final Pattern TABLE_COMMENT_FALLBACK = Pattern.compile("(?i)COMMENT\\s*=\\s*['\"]([^'\"]*)['\"]");

    // 匹配主键定义的正则表达式
    private static final Pattern PRIMARY_KEY_PATTERN = Pattern.compile(
            "PRIMARY\\s+KEY\\s*\\([^)]*`?([^`\\s,)]+)`?[^)]*\\)",
            Pattern.CASE_INSENSITIVE
    );

    // 匹配AUTO_INCREMENT的正则表达式
    private static final Pattern AUTO_INCREMENT_PATTERN = Pattern.compile(
            "`?([^`\\s]+)`?\\s+[^,]*?AUTO_INCREMENT",
            Pattern.CASE_INSENSITIVE
    );

    /**
     * 解析SQL DDL语句
     * 
     * @param ddlSql CREATE TABLE语句
     * @return 解析后的表信息列表
     */
    public List<TableInfo> parseDdl(String ddlSql) {
        List<TableInfo> tableInfoList = new ArrayList<>();
        
        if (StrUtil.isBlank(ddlSql)) {
            return tableInfoList;
        }

        // 分割多个CREATE TABLE语句
        String[] statements = ddlSql.split("(?i)(?=CREATE\\s+TABLE)");
        
        for (String statement : statements) {
            statement = statement.trim();
            if (statement.isEmpty()) {
                continue;
            }
            
            TableInfo tableInfo = parseCreateTableStatement(statement);
            if (tableInfo != null) {
                tableInfoList.add(tableInfo);
            }
        }
        
        return tableInfoList;
    }

    /**
     * 清理表名，移除反引号和其他特殊字符
     */
    private String cleanTableName(String tableName) {
        if (tableName == null) {
            return null;
        }
        // 移除反引号
        tableName = tableName.replaceAll("`", "");
        // 移除其他可能的特殊字符，只保留字母、数字和下划线
        tableName = tableName.replaceAll("[^a-zA-Z0-9_]", "");
        return tableName;
    }

    /**
     * 清理列名，移除反引号和其他特殊字符
     */
    private String cleanColumnName(String columnName) {
        if (columnName == null) {
            return null;
        }
        // 移除反引号
        columnName = columnName.replaceAll("`", "");
        // 移除其他可能的特殊字符，只保留字母、数字和下划线
        columnName = columnName.replaceAll("[^a-zA-Z0-9_]", "");
        return columnName;
    }

    /**
     * 解析单个CREATE TABLE语句
     */
    private TableInfo parseCreateTableStatement(String createTableSql) {
        List<SQLStatement> stmts = SQLUtils.parseStatements(createTableSql, JdbcConstants.MYSQL);
        if (stmts.isEmpty() || !(stmts.get(0) instanceof SQLCreateTableStatement)) {
            return null;
        }

        SQLCreateTableStatement create = (SQLCreateTableStatement) stmts.get(0);

        String tableName = create.getTableName();
        // 清理表名中的反引号和其他特殊字符
        tableName = cleanTableName(tableName);
        TableInfo tableInfo = new TableInfo(tableName);

        if (create.getComment() != null) {
            tableInfo.setTableComment(create.getComment().toString().replaceAll("^['\"]|['\"]$", ""));
        } else {
            Matcher m = TABLE_COMMENT_FALLBACK.matcher(createTableSql);
            if (m.find()) {
                tableInfo.setTableComment(m.group(1));
            }
        }

        // 提取列
        for (SQLTableElement element : create.getTableElementList()) {
            if (element instanceof SQLColumnDefinition) {
                SQLColumnDefinition col = (SQLColumnDefinition) element;
                String name = col.getName().getSimpleName();
                // 清理列名中的反引号和其他特殊字符
                name = cleanColumnName(name);
                String dataType = col.getDataType() != null ? col.getDataType().getName() +
                        (col.getDataType().getArguments().isEmpty() ? "" : col.getDataType().getArguments().toString()) : "";

                ColumnInfo columnInfo = new ColumnInfo(name, dataType);

                if (col.getComment() != null) {
                    columnInfo.setComment(col.getComment().toString().replaceAll("^['\"]|['\"]$", ""));
                }
                columnInfo.setNullable(!col.containsNotNullConstaint());
                columnInfo.setAutoIncrement(col.isAutoIncrement());

                tableInfo.addColumn(columnInfo);
            } else if (element instanceof SQLPrimaryKey) {
                // 在后续主键解析中处理
            }
        }

        // 解析主键（列已填充后进行标记）
        parsePrimaryKey(SQLUtils.toMySqlString(create), tableInfo);
        // 解析自增列（已基于 AST 设置，但也保留回退）
        parseAutoIncrement(SQLUtils.toMySqlString(create), tableInfo);

        return tableInfo;
    }

    /**
     * 解析列定义
     */
    // 解析列由 AST 完成，此处保留方法占位避免模板改动
    private void parseColumns(String columnsDefinition, TableInfo tableInfo) {}

    /**
     * 解析列的其他属性（NOT NULL, DEFAULT等）
     */
    private void parseColumnAttributes(String columnsDefinition, ColumnInfo columnInfo) {
        String columnName = columnInfo.getColumnName();
        
        // 查找该列的完整定义
        Pattern columnDefPattern = Pattern.compile(
                "`?" + Pattern.quote(columnName) + "`?\\s+[^,]*?(?=\\s*,|\\s*$|\\s+PRIMARY|\\s+KEY|\\s+UNIQUE|\\s+INDEX)",
                Pattern.CASE_INSENSITIVE
        );
        
        Matcher matcher = columnDefPattern.matcher(columnsDefinition);
        if (matcher.find()) {
            String columnDef = matcher.group();
            
            // 检查是否允许为空
            columnInfo.setNullable(!columnDef.matches("(?i).*NOT\\s+NULL.*"));
            
            // 检查默认值
            Pattern defaultPattern = Pattern.compile("(?i)DEFAULT\\s+([^\\s,]+)", Pattern.CASE_INSENSITIVE);
            Matcher defaultMatcher = defaultPattern.matcher(columnDef);
            if (defaultMatcher.find()) {
                columnInfo.setDefaultValue(defaultMatcher.group(1));
            }
            
            // 解析长度、精度等
            parseDataTypeDetails(columnInfo.getDbType(), columnInfo);
        }
    }

    /**
     * 解析数据类型的详细信息（长度、精度等）
     */
    private void parseDataTypeDetails(String dataType, ColumnInfo columnInfo) {
        if (dataType == null) return;
        
        // 解析长度 VARCHAR(255), INT(11)等
        Pattern lengthPattern = Pattern.compile("\\w+\\((\\d+)\\)", Pattern.CASE_INSENSITIVE);
        Matcher lengthMatcher = lengthPattern.matcher(dataType);
        if (lengthMatcher.find()) {
            columnInfo.setLength(Integer.parseInt(lengthMatcher.group(1)));
        }
        
        // 解析精度和小数位数 DECIMAL(10,2)等
        Pattern precisionPattern = Pattern.compile("\\w+\\((\\d+)\\s*,\\s*(\\d+)\\)", Pattern.CASE_INSENSITIVE);
        Matcher precisionMatcher = precisionPattern.matcher(dataType);
        if (precisionMatcher.find()) {
            columnInfo.setPrecision(Integer.parseInt(precisionMatcher.group(1)));
            columnInfo.setScale(Integer.parseInt(precisionMatcher.group(2)));
        }
    }

    /**
     * 解析主键
     */
    private void parsePrimaryKey(String columnsDefinition, TableInfo tableInfo) {
        Matcher primaryKeyMatcher = PRIMARY_KEY_PATTERN.matcher(columnsDefinition);
        
        if (primaryKeyMatcher.find()) {
            String primaryKeyColumn = primaryKeyMatcher.group(1);
            
            // 在列信息中标记主键
            for (ColumnInfo column : tableInfo.getColumns()) {
                if (column.getColumnName().equals(primaryKeyColumn)) {
                    column.setPrimaryKey(true);
                    tableInfo.setPrimaryKeyColumn(column);
                    break;
                }
            }
        } else {
            // 如果没有显式的PRIMARY KEY定义，检查列定义中的PRIMARY KEY
            for (ColumnInfo column : tableInfo.getColumns()) {
                Pattern columnPrimaryPattern = Pattern.compile(
                        "`?" + Pattern.quote(column.getColumnName()) + "`?\\s+[^,]*?PRIMARY\\s+KEY",
                        Pattern.CASE_INSENSITIVE
                );
                if (columnPrimaryPattern.matcher(columnsDefinition).find()) {
                    column.setPrimaryKey(true);
                    tableInfo.setPrimaryKeyColumn(column);
                    break;
                }
            }
        }
    }

    /**
     * 解析自增列
     */
    private void parseAutoIncrement(String columnsDefinition, TableInfo tableInfo) {
        Matcher autoIncrementMatcher = AUTO_INCREMENT_PATTERN.matcher(columnsDefinition);
        
        while (autoIncrementMatcher.find()) {
            String autoIncrementColumn = autoIncrementMatcher.group(1);
            
            // 在列信息中标记自增
            for (ColumnInfo column : tableInfo.getColumns()) {
                if (column.getColumnName().equals(autoIncrementColumn)) {
                    column.setAutoIncrement(true);
                    break;
                }
            }
        }
    }

    /**
     * 验证SQL DDL语法
     */
    public boolean validateDdl(String ddlSql) {
        if (StrUtil.isBlank(ddlSql)) {
            return false;
        }
        try {
            List<SQLStatement> statements = SQLUtils.parseStatements(ddlSql, JdbcConstants.MYSQL);
            if (statements.isEmpty()) {
                return false;
            }
            // 至少包含一个 CREATE TABLE 且列不为空
            for (SQLStatement stmt : statements) {
                if (stmt instanceof SQLCreateTableStatement create) {
                    return !create.getTableElementList().isEmpty();
                }
            }
            return false;
        } catch (Exception ex) {
            return false;
        }
    }
}